﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;

using System.Data.SqlClient;
using famails.common;
using famails.dto;

namespace famails.dao
{
    /// <summary>
    /// Summary description for CustomerDAO
    /// </summary>
    public class ClientDAO
    {
        public ClientDAO()
        {

        }

        public bool Customer_Lock_Unlock(int Id, Common.clientStatus status)
        {
            string sql = "UPDATE tblClient SET " +
                        "status = " + (int)status + " where clientid=" + Id;
            if (status == Common.clientStatus.Normal)
                sql += " and status=" + (int)Common.clientStatus.Lock;
            else if (status == Common.clientStatus.Lock)
                sql += " and status=" + (int)Common.clientStatus.Normal;

            SqlCommand cmd = new SqlCommand(sql, ConnectionData._MyConnection);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@clientid", SqlDbType.Int).Value = Id;

            if (ConnectionData._MyConnection.State == ConnectionState.Closed)
            {
                ConnectionData._MyConnection.Open();
            }
            int i = cmd.ExecuteNonQuery();
            cmd.Dispose();
            return i > 0;
        }
        public bool Customer_Active(int Id)
        {
            string sql = "UPDATE tblClient SET " +
                        "status = " + (int)Common.clientStatus.Normal + " where clientid=" + Id;
            sql += " and status=" + (int)Common.clientStatus.InActive;

            SqlCommand cmd = new SqlCommand(sql, ConnectionData._MyConnection);
            cmd.CommandType = CommandType.Text;

            if (ConnectionData._MyConnection.State == ConnectionState.Closed)
            {
                ConnectionData._MyConnection.Open();
            }
            int i = cmd.ExecuteNonQuery();
            if (i > 0)
            {
                sql = "Update tblUserLogin set is_block=0 where userid=(select userid from tblclient where clientid=" + Id + ")";
                cmd.CommandText = sql;
                i = cmd.ExecuteNonQuery();
            }
            cmd.Dispose();
            return i > 0;
        }

        public DataTable Search(string name, string email, string phone, bool isCheckExpire, string from, string to)
        {
            SqlCommand cmd = new SqlCommand("SP_Client_Search_new", ConnectionData._MyConnection);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@name", SqlDbType.NVarChar).Value = name;
            cmd.Parameters.Add("@Email", SqlDbType.NVarChar).Value = email;
            cmd.Parameters.Add("@Phone", SqlDbType.NVarChar).Value = phone;
            //cmd.Parameters.Add("@isCheckExpire", SqlDbType.NVarChar).Value = isCheckExpire;
            cmd.Parameters.Add("@from", SqlDbType.NVarChar).Value = from;
            cmd.Parameters.Add("@to", SqlDbType.NVarChar).Value = to;
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataTable table = new DataTable();
            if (ConnectionData._MyConnection.State == ConnectionState.Closed)
            {
                ConnectionData._MyConnection.Open();
            }
            adapter.Fill(table);
            cmd.Dispose();
            adapter.Dispose();
            return table;
        }

        public DataTable GetByID(int Id)
        {
            SqlCommand cmd = new SqlCommand("Select * from tblClient where userid=@userId", ConnectionData._MyConnection);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@userId", SqlDbType.Int).Value = Id;
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataTable table = new DataTable();
            if (ConnectionData._MyConnection.State == ConnectionState.Closed)
            {
                ConnectionData._MyConnection.Open();
            }
            adapter.Fill(table);
            cmd.Dispose();
            adapter.Dispose();
            return table;
        }
        public DataTable GetByClientID(int Id)
        {
            SqlCommand cmd = new SqlCommand("Select * from tblClient where clientid=@Id", ConnectionData._MyConnection);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@Id", SqlDbType.Int).Value = Id;
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataTable table = new DataTable();
            if (ConnectionData._MyConnection.State == ConnectionState.Closed)
            {
                ConnectionData._MyConnection.Open();
            }
            adapter.Fill(table);
            cmd.Dispose();
            adapter.Dispose();
            return table;
        }
        public DataTable getallclient(int clientId)
        {
            SqlCommand cmd = new SqlCommand("Select * from tblClient where clientId=@clientId ", ConnectionData._MyConnection);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@clientId", SqlDbType.Int).Value = clientId;
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataTable table = new DataTable();
            if (ConnectionData._MyConnection.State == ConnectionState.Closed)
            {
                ConnectionData._MyConnection.Open();
            }
            adapter.Fill(table);
            cmd.Dispose();
            adapter.Dispose();
            return table;
        }

        public DataTable GetByEmail(string Email)
        {
            SqlCommand cmd = new SqlCommand("Select * from tblClient where Email=@Email", ConnectionData._MyConnection);
            //cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@Email", SqlDbType.NVarChar).Value = Email;
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataTable table = new DataTable();
            if (ConnectionData._MyConnection.State == ConnectionState.Closed)
            {
                ConnectionData._MyConnection.Open();
            }
            adapter.Fill(table);
            cmd.Dispose();
            adapter.Dispose();
            return table;
        }
        public void UpdateInfomation(ClientDTO dto)
        {
            string sql = @"Update tblClient set clientName=@clientName,address=@address,phone=@phone,dateofbirth=@dateofbirth 
                           --, CompanyName=@CompanyName,CompanyLogo=@CompanyLogo,CompanyEmail=@CompanyEmail,CompanyPhone=@CompanyPhone,CompanyAddress=@CompanyAddress,CompanyFax=@CompanyFax
                           where clientId=@clientId";
            SqlCommand cmd = new SqlCommand(sql, ConnectionData._MyConnection);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@clientId", SqlDbType.Int).Value = dto.ClientId;
            cmd.Parameters.Add("@clientName", SqlDbType.NVarChar).Value = dto.ClientName;
            cmd.Parameters.Add("@address", SqlDbType.NVarChar).Value = dto.Address;
            cmd.Parameters.Add("@phone", SqlDbType.NVarChar).Value = dto.Phone;
            cmd.Parameters.Add("@dateofbirth", SqlDbType.DateTime).Value = dto.DayOfBirth;

            //cmd.Parameters.Add("@CompanyEmail", SqlDbType.NVarChar).Value = dto.CompanyEmail;
            //cmd.Parameters.Add("@CompanyName", SqlDbType.NVarChar).Value = dto.CompanyName;
            //cmd.Parameters.Add("@CompanyPhone", SqlDbType.NVarChar).Value = dto.CompanyPhone;
            //cmd.Parameters.Add("@CompanyFax", SqlDbType.NVarChar).Value = dto.CompanyFax;
            //cmd.Parameters.Add("@CompanyLogo", SqlDbType.NVarChar).Value = dto.CompanyLogo;
            //cmd.Parameters.Add("@CompanyAddress", SqlDbType.NVarChar).Value = dto.CompanyAddress;
            cmd.ExecuteNonQuery();
        }
        public void UpdateExtendLicense(string clientid, DateTime activeday, DateTime expireday)
        {
            string sql = "Update tblClient set activeDate=@activeDate,expireDate=@expireDate where clientId=@clientId";
            SqlCommand cmd = new SqlCommand(sql, ConnectionData._MyConnection);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@clientId", SqlDbType.Int).Value = clientid;
            cmd.Parameters.Add("@activeDate", SqlDbType.DateTime).Value = activeday;
            cmd.Parameters.Add("@expireDate", SqlDbType.DateTime).Value = expireday;
            cmd.ExecuteNonQuery();
        }
        public void UpdateRegiterId(int clientId, string activeDay, string expireDay, int lastRegisterId, int registerId)
        {
            string sql = "set dateformat dmy Update tblClient set activeDate=@activeDate,expireDate=@expireDate,lastRegisterId=@lastRegisterId, registerId=@registerId where clientId=@clientId";
            SqlCommand cmd = new SqlCommand(sql, ConnectionData._MyConnection);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@clientId", SqlDbType.Int).Value = clientId;
            cmd.Parameters.Add("@activeDate", SqlDbType.VarChar).Value = activeDay;
            cmd.Parameters.Add("@expireDate", SqlDbType.VarChar).Value = expireDay;
            cmd.Parameters.Add("@lastRegisterId", SqlDbType.Int).Value = lastRegisterId;
            cmd.Parameters.Add("@registerId", SqlDbType.Int).Value = registerId;
            cmd.ExecuteNonQuery();
        }

        internal bool Delete(int clinetId)
        {
            string sql = "SP_Client_Delete";
            SqlCommand cmd = new SqlCommand(sql, ConnectionData._MyConnection);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@clientId", SqlDbType.Int).Value = clinetId;
            object obj = cmd.ExecuteScalar();
            int i = 0;
            int.TryParse(obj + "", out i);
            return i > 0;
        }

        internal DataTable LoadThongTinDangKy(int id)
        {
            SqlCommand cmd = new SqlCommand("SP_Client_LoadThongTinDangKy", ConnectionData._MyConnection);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@clientID", SqlDbType.Int).Value = id;
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataTable table = new DataTable();
            if (ConnectionData._MyConnection.State == ConnectionState.Closed)
            {
                ConnectionData._MyConnection.Open();
            }
            adapter.Fill(table);
            cmd.Dispose();
            adapter.Dispose();
            return table;
        }

        internal bool UpdatePayment(string receiverId)
        {
            int registerid, userid;
            int.TryParse(receiverId.Split('-')[0], out userid);
            int.TryParse(receiverId.Split('-')[1], out registerid);
            if (userid > 0 && registerid > 0)
            {
                SqlTransaction tran = null;
                try
                {
                    string sql = @"update tblclientregister set ispaid=1, paiddate = getdate() where registerid=@registerid and ispaid=0";
                    SqlCommand cmd = new SqlCommand(sql, ConnectionData._MyConnection);
                    tran = cmd.Connection.BeginTransaction();
                    cmd.Transaction = tran;
                    cmd.Parameters.Add("@registerid", SqlDbType.Int).Value = registerid;
                    int result = cmd.ExecuteNonQuery();
                    if (result > 0)
                    {
                        sql = "SELECT [to] FROM [tblClientRegister] c  where registerid=@registerid ";
                        cmd = new SqlCommand(sql, ConnectionData._MyConnection);
                        cmd.Transaction = tran;
                        cmd.Parameters.Add("@registerid", SqlDbType.Int).Value = registerid;
                        object expiredate = cmd.ExecuteScalar();
                        if (expiredate+""!="")
                        {
                            DateTime date = Convert.ToDateTime(expiredate);
                            sql = "set dateformat dmy update tblClient set userid = @userid, lastregisterid=registerid, registerid=@registerid, expiredate= @expiredate  where userid=@userid";
                            cmd = new SqlCommand(sql, ConnectionData._MyConnection);
                            cmd.Transaction = tran;
                            cmd.CommandType = CommandType.Text;
                            cmd.Parameters.Add("@registerid", SqlDbType.Int).Value = registerid;
                            cmd.Parameters.Add("@userid", SqlDbType.Int).Value = userid;
                            cmd.Parameters.Add("@expiredate", SqlDbType.NVarChar).Value = date.ToString("dd/MM/yyyy");
                            int i = cmd.ExecuteNonQuery();
                            tran.Commit();
                            return i > 0;

                        }
                        else tran.Rollback();
                    }
                    else tran.Rollback();
                }
                catch (Exception ex)
                { tran.Rollback(); throw ex; }
            }
            return false;
        }

        internal bool CancelTransaction(string registerid)
        {
            SqlTransaction tran = null;
            try
            {

                string sql = @"delete from tblclientregister where registerid=@registerid and ispaid=0";
                SqlCommand cmd = new SqlCommand(sql, ConnectionData._MyConnection);
                tran = cmd.Connection.BeginTransaction();
                cmd.Transaction = tran;
                cmd.Parameters.Add("@registerid", SqlDbType.Int).Value = registerid;
                int result = cmd.ExecuteNonQuery();
                if (result > 0)
                {
                    sql = "delete from tblClientFunction where registerid=@registerid";
                    cmd = new SqlCommand(sql, ConnectionData._MyConnection);
                    cmd.Transaction = tran;
                    cmd.Parameters.Add("@registerid", SqlDbType.Int).Value = registerid;
                    result = cmd.ExecuteNonQuery();

                    tran.Commit();
                    return result > 0;
                }
                else tran.Rollback();
            }
            catch (Exception ex)
            { tran.Rollback(); throw ex; }
            return false;
        }
    }
}